OpenEdge Data Management:<br />DataServer for Microsoft SQL Server Error handling

One common data entry error is attempting to add a duplicate record; that is, trying to create a record using a unique key that already exists in the database. Suppose, for example, that a user tries to add a customer with cust-num = 1 (where cust-num is a unique key), but a customer record with this cust-num value already exists. The attempt fails and an error is generated.

When this type of error occurs, the 4GL run time engine tries to resolve it by working back through the procedure, looking at each block header until the closest block with the error-handling property is found, and then undoing and retrying the block. (See OpenEdge Development: Progress 4GL Handbook for more information about error handling.) However, because the DataServer is accessing a non-OpenEdge data source, duplicate-key errors can not be detected until the end of a transaction block. Therefore, if an error occurs in a subtransaction, it is not detected until the end of the entire transaction block and default error handling must be performed for the entire transaction block.

The following example illustrates OpenEdge database and DataServer error handling:

rep-blk: REPEAT: PROMPT-FOR customer.cust-num. /* User input / FIND customer USING cust-num NO-ERROR. IF AVAILABLE customer THEN UPDATE customer.cust-num name customer.state. / User input / do-blk: DO ON ERROR UNDO do-blk, RETRY do-blk: FIND state WHERE st.state = customer.state. DISPLAY state. SET state. / User input */ END. END.

rep-blk: 
REPEAT: 
    PROMPT-FOR customer.cust-num. /* User input */ 
    FIND customer USING cust-num NO-ERROR. 
    IF AVAILABLE customer THEN  
        UPDATE customer.cust-num name customer.state. /* User input */ 
        do-blk: 
        DO ON ERROR UNDO do-blk, RETRY do-blk: 
            FIND state WHERE st.state = customer.state. 
            DISPLAY state. 
            SET state. /* User input */ 
        END. 
END.

This procedure displays the following screen, in which the user is prompted to enter data into the cust-num field and then the state field:

Suppose that the user enters an existing state (for example, NH) while the 4GL is processing the DO block. When this duplicate-key entry occurs for an OpenEdge database, the 4GL returns control to the DO block, displays a message that the record exists, and reprompts the user for a state abbreviation.

However, with the DataServer, if a duplicate key entry occurs in the DO block, the 4GL returns control to the REPEAT block rather than the DO block. As a result, the procedure reprompts the user for a customer number after the inner transaction completes:

If you use NO–ERROR to do your own error handling, you must account for the fact that an MSS data source creates or updates a record later than an OpenEdge database does. For example, the following code does not trap data source errors, because the requests to perform the operations have not yet been sent to the data source:


CREATE customer NO-ERROR. ASSIGN cust-num = 45 NO-ERROR. ASSIGN name = "Smith" NO-ERROR.

The VALIDATE statement causes the DataServer to send requests to your MSS data source, so incorporate it into your error-handling technique, as in the following example:

DEFINE VAR j AS INTEGER. DO TRANSACTION: CREATE customer NO-ERROR. ASSIGN cust-num = 45 NO-ERROR. VALIDATE customer. IF ERROR-STATUS:ERROR THEN DO: MESSAGE "error: number of messages = " ERROR-STATUS:NUM-MESSAGES. DO j = 1 TO ERROR-STATUS:NUM-MESSAGES: MESSAGE "error" ERROR-STATUS:GET-NUMBER(j) ERROR-STATUS:GET-MESSAGE (j). END. UNDO, LEAVE. END. ASSIGN name = "Smith" NO-ERROR. VALIDATE customer. IF ERROR-STATUS:ERROR THEN . . . END.

DEFINE VAR j AS INTEGER.
DO TRANSACTION:
  CREATE customer NO-ERROR.

  ASSIGN cust-num = 45 NO-ERROR.
  VALIDATE customer.
  IF ERROR-STATUS:ERROR THEN DO:
    MESSAGE "error: number of messages = " ERROR-STATUS:NUM-MESSAGES.
    DO j = 1 TO ERROR-STATUS:NUM-MESSAGES:
      MESSAGE "error" ERROR-STATUS:GET-NUMBER(j)
          ERROR-STATUS:GET-MESSAGE (j).
    END.
    UNDO, LEAVE.
  END.

  ASSIGN name = "Smith" NO-ERROR.
  VALIDATE customer.
  IF ERROR-STATUS:ERROR THEN . . .
END.

Another difference in behavior occurs when two users simultaneously attempt to create records with duplicate keys. An OpenEdge database raises an error immediately, but the data source raises an error after the first transaction commits and only if the second transaction does not roll back. To avoid this difference, either change the scope of the transaction so that it completes more quickly or make the key nonunique and enforce uniqueness at the application level. Another technique is to use a RELEASE or VALIDATE statement when you check for the key’s uniqueness.